package com.example.springbootfoodsafety.dao;

import com.example.springbootfoodsafety.entity.User;
import com.example.springbootfoodsafety.po.Article;
import com.example.springbootfoodsafety.po.Speak;
import com.example.springbootfoodsafety.po.Tag;
import com.example.springbootfoodsafety.util.Result;
import org.apache.ibatis.annotations.*;
import org.springframework.data.relational.core.sql.In;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ManageDao {

/**********************************    所有的用户操作     ******************************************************/
    /**
     * 添加用户
     * */
    @Insert("INSERT INTO common_user (user_name,`password`,create_data,email,nick_name,sex,address,age,birthday) \n" +
            " VALUES(#{uname},#{password},DATE_SUB(curdate(),INTERVAL 0 DAY),#{eml},#{nickName},#{sex},#{address},#{age},#{birthday}) \n")
    public boolean addUser(User user);
    /**
     * 登录方法
     * */
    @Select("SELECT id ,user_name AS uname,address,age,birthday,email eml,avatar headImg,sex,nickName,identity FROM common_user WHERE user_name=#{name} and `password`=#{password}")
    public User login(@Param("name")String name, @Param("password") String password);
    @Update("UPDATE common_user SET status=1 WHERE id =#{id}")
    public boolean loginStatus(Integer id);

    /***
     * 根据id删除用户
     * @param id
     * @return
     */
    @Delete("DELETE FROM common_user WHERE id=#{id}")
    public boolean deleteUser(Integer id);

/***
     * 根据昵称获取id
     * @param name
     * @return
     */
    @Select("SELECT COUNT(id) FROM common_user WHERE user_name=#{name}")
    public Integer forNameGetId(String name);

    /***
     * 获取全部用户
     * @return
     */
    @Select("select id,user_name uname,age,sex,address,birthday,avatar headImg,if_deleted as 'delete' from common_user")
    public List<User> getAllUser();
    /***
     * 获取全部的线上用户
     * @return
     */
    @Select("SELECT id,user_name AS uname,address,age,birthday,email eml,avatar headImg,sex,nickName FROM common_user WHERE status=1")
    public List<User> getOnLineUser();

    /***
     * 根据名字判断是否重名
     * @param name
     * @return
     */
    @Select("SELECT id FROM common_user WHERE nick_name=#{name}")
    public boolean modifyName(String name);
    /**
     * 禁止用户
     * */
    @Update("UPDATE common_user SET deleted=0 WHERE id=#{id}")
    public boolean banUser(int id);

    /**
     * 解封用户
     * */
    @Update("UPDATE common_user SET deleted=1 WHERE id=#{id}")
    public boolean UnsealUser(int id);
    /***
     * 修改头像功能
     */
    @Update("UPDATE common_user SET avatar=#{modify} WHERE id = #{id}")
    public boolean modifyHeadImg(@Param("id") Integer id,@Param("modify") String modify);

    /**
     * 注销方法
     * @param id
     * @return
     */
    @Update("UPDATE common_user SET status=0 WHERE id =#{id}")
    public boolean logout(Integer id);

    @Update("UPDATE common_user SET password=#{password} WHERE id=#{id}")
    boolean modifyPassword(@Param("id") Integer id,@Param("password") String password);
    @Select("SELECT * FROM common_user WHERE id=1 AND password=123")
    User findPassword(@Param("id") Integer id,@Param("password") String password);

    /**********************************    所有的文章操作     ******************************************************/

    /**
     *
     * 前十条文章展示
     * 从后端获取到文章（标题、简介、作者、日期、点赞量、图片）
     * */
    @Select("SELECT m.id,m.author_id,body,m.title,m.summary synopsis,m.create_time dateline,m.view_counts viewCounts,comment_counts commentCounts,m.img,u.avatar authorheaderImg,u.user_name author from my_article m LEFT JOIN common_user u on m.author_id=u.id where m.static=2")
    List<Article> getArticle();

    @Select("SELECT m.id,body,m.title,m.summary synopsis,m.create_time dateline,m.view_counts viewCounts,comment_counts commentCounts,m.img,u.avatar authorheaderImg,u.user_name author from my_article m LEFT JOIN common_user u on m.author_id=u.id where m.static=1")
    List<Article> getArticleNoStatic();
    @Update("UPDATE my_article SET static=#{static} WHERE id=#{id}")
    boolean  modifyArticleStatic(@Param("id") Integer id, @Param("static") Integer artcle_static);
    @Delete("DELETE FROM my_article WHERE id=#{id}")
    boolean deleteArtice(Integer id);
    /***
     * 根据日期获取全部的文章
     * @return
     */
    @Select("SELECT m.id,m.title,m.summary synopsis,m.create_time dateline,m.body,m.view_counts viewCounts,m.img,u.user_name author,u.avatar authorheaderImg from my_article m LEFT JOIN common_user u on m.author_id=u.id where m.static=2 GROUP  BY dateline desc ,id desc  LIMIT 6 ;")
    List<Article>  getLatestArticle();

    @Select("SELECT m.id,m.title,m.summary synopsis,m.create_time dateline,m.body,m.view_counts viewCounts,m.img,u.user_name author,u.avatar authorheaderImg from my_article m LEFT JOIN common_user u on m.author_id=u.id where m.static=2 AND m.categorty_id=#{tagID}  GROUP  BY dateline desc ,id desc;")
    List<Article>  getTagArticle(Integer tagID);
    /***
     * 根据点赞量获取前6篇文章
     * @return
     */
    @Select("SELECT m.id,m.title,m.summary synopsis,m.create_time dateline,m.body,m.view_counts viewCounts,m.img,u.user_name author,u.avatar authorheaderImg from my_article m LEFT JOIN common_user u on m.author_id=u.id where m.static=2 GROUP BY view_counts desc ,id desc  LIMIT 6 ;")
    List<Article> getHeatRankingArticle();

    /***
     * 修改点赞
     * @param add
     * @param id
     * @return
     */
    @Update("UPDATE my_article SET view_counts=#{add} WHERE id=#{id]")
    public Integer AddThumbsUp(@Param("add") Integer add,@Param("id") Integer id);

    /**
     * 写博客
     * 需要传入的数据
     *    1.作者
     *    2.文章内容
     *    3.文章类别
     *    4.文章标题
     *    5. 文章简介
     *
     * */
    @Insert("INSERT INTO my_article(create_time,summary,title,author_id,categorty_id,img,body) VALUES(DATE_SUB(curdate(),INTERVAL 0 DAY),#{summary},#{title},#{author_id},#{tag},#{img},#{body})")
    boolean addArticle(Article article);


/********************************* 评论的相关方法 ************************/

    /***
     * 根据文章id获取到文章下面全部的评论
     * @param id
     * @return
     */
    @Select("SELECT S.id,S.speak_data speankDate,S.speak_content speakContent,U.user_name speakPerson FROM speak S LEFT JOIN common_user U ON S.speak_person=U.id WHERE S.speak_article =#{id}")
    List<Speak> getArticleSpeak(Integer id);
    @Insert("insert into speak(speak_person,speak_data,speak_content,speak_article) values(#{speakPerson},DATE_SUB(curdate(),INTERVAL 0 DAY),#{speakContent},#{speakArticle})")
     boolean addSpeak(Speak speak);
    @Delete("DELETE FROM speak WHERE id=#{id}")
     boolean deleteSpeak(Integer id);


    /**********************************    所有的类别操作     ******************************************************/
    /**
     * 管理员添加类别
     * */
    @Insert("insert into My_tag(tag_name) VALUES(#{tagName})")
    public  boolean addTag(String tagName);
    /**
     * 删除类别
     * */
    @Update("DELETE FROM my_tag WHERE id=#{id}")
    public boolean deleteTag(int id);
    @Select("SELECT id,tag_name tagName FROM my_tag")
    public List<Tag> getAlltag();

    /***
     * 展示所有标签以及对应的文章数量
     * @return
     */
    @Select("SELECT T.id,T.tag_name as tagName,COUNT(A.id) as count FROM my_article A LEFT JOIN my_tag T ON  A.categorty_id=T.id WHERE A.static=2 GROUP BY A.categorty_id;")
      List<Tag> showAllTagAndCount();
    @Select("SELECT m.id,m.title,m.summary synopsis,m.create_time dateline,m.body,m.view_counts viewCounts,m.img,u.user_name author,u.avatar authorheaderImg from my_article m LEFT JOIN common_user u on m.author_id=u.id WHERE m.categorty_id=#{id}")
     List<Article> showAtrcleWhereTag(Integer id);


}
